import requests
import json
import xlwt
from xlwt import Style


def get_html(url):
    proxy = {
        'http': '120.25.253.234:812',
        'https': '163.125.222.244:8123'
    }
    heads = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.146 Safari/537.36',
        'Connection': 'keep-alive',
        'Request Method': 'GET',
        'Referrer Policy': 'no-referrer-when-downgrade',
        'Request URL': 'https://www.bfindex.com/Api/RestApiLite/fixtures',
        'Accept': 'application/json, text/plain, */*',
        'Referer': 'https://www.bfindex.com/sc/Lite/',
        'authority': 'www.bfindex.com',
        'verify': 'False'
    }

    req = requests.get(url, headers=heads)
    return req;


# 设置居中
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
alignment.vert = xlwt.Alignment.VERT_TOP  # 垂直方向

# 设置字体
font = xlwt.Font()
font.bold = True

# 定义不同的excel style
styleHead = xlwt.XFStyle()
styleHead.font = font
styleHead.alignment = alignment

styleContent = xlwt.XFStyle()
styleContent.alignment = alignment


def get_ipport(req):
    list = req.json()
    print(list)
    return list


def write_excel(list):
    '''
    第二种方式，自动判断需要合并的单元格范围
    :return:
    '''
    table_head = ['日期', '主队-客队', '得分', '当前', '胜负平赔率', '买卖资金', '必发指数']

    # 初始化workbook 并且添加excel Sheet
    workbook = xlwt.Workbook(encoding='utf-8')
    xlsheet = workbook.add_sheet("excel", cell_overwrite_ok=True)

    # 写excel表头
    headlen = len(table_head)
    for i in range(headlen):
        xlsheet.write(0, i, table_head[i], style=styleHead)

    contentRow = len(list)  # 列表元素个数  = 待写入内容行数

    # 从content获取要写入的第一列的内容,存入列表
    # first_col = []
    # for i in range(contentRow):
    #     first_col.append(list[i][0])
    # print("first_col", first_col)

    # 去掉列表中重复元素，并且顺序不变
    # nfirst_col = list(set(first_col))
    # nfirst_col.sort(key=first_col.index)  # sort排序与原顺序一致
    # print("nfirst_col", nfirst_col)
    #
    row = 1
    # for i in nfirst_col:
    #     count = first_col.count(i)  # 计算元素的重复个数，比如测试 ：3
    #     uprange = row + count - 1  # 合并范围后的上行数
    #     xlsheet.write_merge(row, uprange, 0, 0, i)  # 合并单元格写入内容
    #     row = uprange + 1  # 从下一行开始写入
    for row in range(row, contentRow):
        xlsheet.write(row, 0, list[row]['matchdatetime'], style=styleContent)
        xlsheet.write(row, 1, list[row]['hometeam'] + '-' + list[row]['awayteam'] + "(主队在前)", style=styleContent)
        xlsheet.write(row, 2, str(list[row]['homescore']) + '-' + str(list[row]['awayscore']), style=styleContent)
        xlsheet.write(row, 3, list[row]['status'], style=styleContent)
        xlsheet.write(row, 4,
                      str(list[row]['home_lastpricetraded']) + '-' + str(list[row]['draw_lastpricetraded']) + '-' + str(
                          list[row][
                              'away_lastpricetraded']), style=styleContent)
        xlsheet.write(row, 5, str(list[row]['home_totalamountmatched']) + '-' + str(
            list[row]['draw_totalamountmatched']) + '-' +
                      str(list[row]['away_totalamountmatched']), style=styleContent)
        xlsheet.write(row, 6, str(list[row]['home_index']) + '-' + str(list[row]['draw_index']) + '-' + str(
            list[row]['away_index']), style=styleContent)

    workbook.save('E:\excel写入.xls')


if __name__ == '__main__':
    url = 'https://www.bfindex.com/Api/RestApiLite/fixtures'
    write_excel(get_ipport(get_html(url)))
